package wiki.cn.edu.tju;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DataFilter {
	
	public static void main(String[] args) throws ClassNotFoundException, IOException{
		queryText();
//		queryCategorylinks();
//		queryCategory();
//		queryExternallinks();
//		queryImage();
//		queryOldImage();
//		queryTemplatelinks();
//		queryRedirct();
	}
	
		private static void queryOldImage() throws ClassNotFoundException, IOException {
			// TODO Auto-generated method stub
			String sqlStr = "SELECT * FROM my_wikidb.oldimage;";
			try {
				Class.forName(DataCon.driver);
				Connection conn = DriverManager.getConnection(DataCon.url, DataCon.user, DataCon.password);
				Statement stat = null;
				ResultSet rs = null;
				
				try{
					stat = conn.createStatement();
					rs = stat.executeQuery(sqlStr);
					System.out.println(sqlStr);
					int idd = 0;
					while (rs.next()) {
						idd++;
						try {
							String sBlob = "";
							InputStream in = rs.getBinaryStream("oi_name");
							byte[] buffer = new byte[1024];
							int len = 0;
							while((len=in.read(buffer)) != -1){
								sBlob += new String(buffer, 0, len);
							}
							in.close();
							String s= new String(sBlob.getBytes("gb2312"), "gbk");
							System.out.println(s);
						} catch (UnsupportedEncodingException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						
//						if(idd>30600)
//							break;
						if(idd>27600)
							break;
					}
					System.out.println("--------over-------");
				}catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} finally {
					rs.close();
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	private static void queryRedirct() throws ClassNotFoundException, IOException {
		// TODO Auto-generated method stub
		String sqlStr = "SELECT * FROM my_wikidb.redirect where rd_from=282235;";
		try {
			Class.forName(DataCon.driver);
			Connection conn = DriverManager.getConnection(DataCon.url, DataCon.user, DataCon.password);
			Statement stat = null;
			ResultSet rs = null;
			
			try{
				stat = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				System.out.println(sqlStr);
				while (rs.next()) {
					try {
						String sBlob = "";
						InputStream in = rs.getBinaryStream("rd_title");
						byte[] buffer = new byte[1024];
						int len = 0;
						while((len=in.read(buffer)) != -1){
							sBlob += new String(buffer, 0, len);
						}
						in.close();
						String s= new String(sBlob.getBytes("gb2312"), "gbk");
						System.out.println(s);
					} catch (UnsupportedEncodingException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
				System.out.println("--------over-------");
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				rs.close();
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static void queryTemplatelinks() throws ClassNotFoundException, IOException {
		// TODO Auto-generated method stub
		String sqlStr = "SELECT tl_title FROM my_wikidb.templatelinks;";
		try {
			Class.forName(DataCon.driver);
			Connection conn = DriverManager.getConnection(DataCon.url, DataCon.user, DataCon.password);
			Statement stat = null;
			ResultSet rs = null;
			System.out.println("ResultSet rs = null;");
			try{
				System.out.println("try{");
				stat = conn.createStatement();
				System.out.println("stat = conn.createStatement();");
				rs = stat.executeQuery(sqlStr);
				System.out.println(sqlStr);
				while (rs.next()) {
					try {
						String sBlob = "";
						InputStream in = rs.getBinaryStream("tl_title");
						byte[] buffer = new byte[1024];
						int len = 0;
						while((len=in.read(buffer)) != -1){
							sBlob += new String(buffer, 0, len);
						}
						in.close();
						String s= new String(sBlob.getBytes("gb2312"), "gbk");
						System.out.println(s);
					} catch (UnsupportedEncodingException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
				System.out.println("--------over-------");
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				rs.close();
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static void queryImage() throws ClassNotFoundException, IOException {
		// TODO Auto-generated method stub
		String sqlStr = "SELECT * FROM my_wikidb.image;";
		try {
			Class.forName(DataCon.driver);
			Connection conn = DriverManager.getConnection(DataCon.url, DataCon.user, DataCon.password);
			Statement stat = null;
			ResultSet rs = null;
			
			try{
				stat = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				System.out.println(sqlStr);
				int idd = 0;
				while (rs.next()) {
					idd++;
					try {
						String sBlob = "";
						InputStream in = rs.getBinaryStream("img_name");
						byte[] buffer = new byte[1024];
						int len = 0;
						while((len=in.read(buffer)) != -1){
							sBlob += new String(buffer, 0, len);
						}
						in.close();
						String s= new String(sBlob.getBytes("gb2312"), "gbk");
						System.out.println(s);
//						if(s.contains("Historian"))
//							System.out.println(s);
					} catch (UnsupportedEncodingException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
					try {
						String sBlob = "";
						InputStream in = rs.getBinaryStream("img_user");
						byte[] buffer = new byte[1024];
						int len = 0;
						while((len=in.read(buffer)) != -1){
							sBlob += new String(buffer, 0, len);
						}
						in.close();
						String s= new String(sBlob.getBytes("gb2312"), "gbk");
						System.out.println(s);
					} catch (UnsupportedEncodingException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					if(idd>30600)
						break;
//					if(idd>27600)
//						break;
				}
				System.out.println("--------over-------");
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				rs.close();
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static void queryExternallinks() throws ClassNotFoundException, IOException {
		// TODO Auto-generated method stub
		String sqlStr = "SELECT * FROM my_wikidb.externallinks where el_from=22;";
		try {
			Class.forName(DataCon.driver);
			Connection conn = DriverManager.getConnection(DataCon.url, DataCon.user, DataCon.password);
			Statement stat = null;
			ResultSet rs = null;
			
			try{
				stat = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				System.out.println(sqlStr);
				while (rs.next()) {
					try {
						String sBlob = "";
						InputStream in = rs.getBinaryStream("el_to");
						byte[] buffer = new byte[1024];
						int len = 0;
						while((len=in.read(buffer)) != -1){
							sBlob += new String(buffer, 0, len);
						}
						in.close();
						String s= new String(sBlob.getBytes("gb2312"), "gbk");
						System.out.println(s);
					} catch (UnsupportedEncodingException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
				System.out.println("--------over-------");
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				rs.close();
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static void queryCategory() throws ClassNotFoundException, IOException {
		// TODO Auto-generated method stub
		String sqlStr = "SELECT * FROM my_wikidb.category where cat_id=18535;";
		try {
			Class.forName(DataCon.driver);
			Connection conn = DriverManager.getConnection(DataCon.url, DataCon.user, DataCon.password);
			Statement stat = null;
			ResultSet rs = null;
			
			try{
				stat = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				System.out.println(sqlStr);
				while (rs.next()) {
					try {
						String sBlob = "";
						InputStream in = rs.getBinaryStream("cat_title");
						byte[] buffer = new byte[1024];
						int len = 0;
						while((len=in.read(buffer)) != -1){
							sBlob += new String(buffer, 0, len);
						}
						in.close();
						String s= new String(sBlob.getBytes("gb2312"), "gbk");
						System.out.println(s);
					} catch (UnsupportedEncodingException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
				System.out.println("--------over-------");
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				rs.close();
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private static void queryCategorylinks() throws ClassNotFoundException, IOException {
		// TODO Auto-generated method stub
		String sqlStr = "SELECT * FROM my_wikidb.categorylinks where cl_from=22;";
		try {
			Class.forName(DataCon.driver);
			Connection conn = DriverManager.getConnection(DataCon.url, DataCon.user, DataCon.password);
			Statement stat = null;
			ResultSet rs = null;
			
			try{
				stat = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				System.out.println(sqlStr);
				while (rs.next()) {
					try {
						String sBlob = "";
						InputStream in = rs.getBinaryStream("cl_to");
						byte[] buffer = new byte[1024];
						int len = 0;
						while((len=in.read(buffer)) != -1){
							sBlob += new String(buffer, 0, len);
						}
						in.close();
						String s= new String(sBlob.getBytes("GB2312"), "gbk");
						System.out.println(s);
					} catch (UnsupportedEncodingException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
				System.out.println("--------over-------");
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				rs.close();
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void queryText() throws ClassNotFoundException, IOException {
		long pageNum=0, relationNum=0, errorNum=0;
		
		//历史：28366671  文物：27765866 后母戊鼎：27061232 鼎:26473347 九鼎: 28383057
		//青铜器: 26949524  毛公鼎： 28398904 古希腊：26987345 集：22948164 集部：16518041
		//集部：22948113
		int curTextId = 28366671;
		
		String sqlStr = "SELECT page_id,page_latest FROM my_wikidb.my_wikidbpage;";
//		String sqlStr = "SELECT old_text FROM my_wikidb.my_wikidbtext where old_id="+curTextId+";";
//		String sqlStr = "SELECT * FROM my_wikidb.redirect where rd_from=3037976;";
		try {
			Class.forName(DataCon.driver);
			Connection conn = DriverManager.getConnection(DataCon.url, DataCon.user, DataCon.password);
			Statement stat = null;
			ResultSet rs = null;
			Statement statTxt = null;
			ResultSet rsTxt = null;
			
			try{
				stat = conn.createStatement();
				statTxt = conn.createStatement();
				rs = stat.executeQuery(sqlStr);
				
				int curPageId, relPageId, relateType = 0;
				String insertStr = null;
				String relIdStr, queryTextStr;
				while (rs.next()) {
					pageNum++;
					try {
						curTextId = rs.getInt("page_latest");
						System.out.println(curTextId);
						curPageId = rs.getInt("page_id");
						queryTextStr = "SELECT old_text FROM my_wikidb.my_wikidbtext where old_id="+curTextId+";";
						rsTxt = statTxt.executeQuery(queryTextStr);
						if(rsTxt.next()){
							String sBlob = "";
							InputStream in = rsTxt.getBinaryStream("old_text");
	//						InputStream in = rs.getBinaryStream("rd_title");
							byte[] buffer = new byte[1024];
							int len = 0;
							while((len=in.read(buffer)) != -1){
								sBlob += new String(buffer, 0, len);
							}
							in.close();
							
							String s= new String(sBlob.getBytes("UTF-8"), "UTF-8");
//							System.out.println(s);
							String [] pages = quote(s, "\\[\\[", "\\]\\]");
							for (int i = 0; i < pages.length; i++) {
								relateType = 0;
								if(pages[i].contains(":") && !pages[i].startsWith("Category:")){
									continue;
								}
								if(pages[i].endsWith("|*")){//本身是category
									continue;
	//								pages[i] = pages[i].substring(0, pages[i].length()-2);
								}
								if(pages[i].contains("|")){//本身是category
									String [] strTemp = pages[i].split("\\|");
									pages[i] = strTemp[strTemp.length-1];
								}
								if(pages[i].startsWith("Category:"))
								{
									pages[i] = pages[i].substring(9, pages[i].length());
									relateType = 1;
								}
								System.out.println(pages[i]);
								relIdStr = "SELECT page_id FROM my_wikidb.my_wikidbpage where page_title = '"+pages[i]+"';";
								try {
									relPageId = DataCon.getNewItemId(null, relIdStr, "page_id");
									insertStr = "insert into my_wikidb.page_relation (page_id_a, page_id_b,page_relation_type) values("+curPageId+","+relPageId+","+relateType+");";
									DataCon.insertSQL(null, insertStr);
									relationNum ++;
								} catch (Exception e) {
									// TODO: handle exception
									errorNum ++;
									System.out.println("error: not find: "+pages[i]);
								}
							}
						}
					} catch (UnsupportedEncodingException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
				System.out.println("--------over-------   pageNum="+pageNum+"  relationNum="+relationNum+"   errorNum="+errorNum);
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				if(rs!=null){
					rs.close();
				}
				
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static String[] quote(String srcStr, String sigHead, String sigTail){
		if("".equals(srcStr) || srcStr == null){
			return null;
		}
		else{
			String [] srcHead = srcStr.split(sigHead);
			String [] srcTail = new String[srcHead.length-1];
			for (int i = 1; i < srcHead.length; i++) {
				srcTail[i-1] = srcHead[i].split(sigTail)[0];
			}
			return srcTail;
		}
	}
}
